package edu.nctu.csie.jichang.database.model.builder;

import java.sql.Types;
import java.util.List;
import java.util.Map;

import edu.nctu.csie.jichang.database.dbinfo.DataBaseInfo;
import edu.nctu.csie.jichang.database.dbinfo.LoginInfo;
import edu.nctu.csie.jichang.database.model.cell.ColumnFullInfo;
import edu.nctu.csie.jichang.database.model.cell.ColumnInfo;
import edu.nctu.csie.jichang.database.model.cell.DBTable;
import edu.nctu.csie.jichang.database.model.cell.NameMap;
import edu.nctu.csie.jichang.database.model.cell.NameMapCell;
import edu.nctu.csie.jichang.database.util.Configuration;
import edu.nctu.csie.jichang.database.util.StringUtil;

public abstract class AbstractSQLBuilder implements ISQLBuilder {

//	private static final Logger LOG = LoggerFactory.getLogger(AbstractSQLBuilder.class);
	
	protected static final String CHANGE_LINE = StringUtil.CHANGE_LINE;
	protected static final String CHANGE_TAB  = StringUtil.TAB;
	
	protected String ignoreAlterReference = Configuration.getInstance().isIgnoreAlterReference()?"-- " :"" ;
	protected String ignoreDropTable      = Configuration.getInstance().isIgnoreDropTable()?"-- " :"" ;
	
	protected String symbleEndOfFunction = ";";
	protected String symbleEndOfStatment = ",";
	
	public static ISQLBuilder getSQLBuilder(LoginInfo pInfo){
		switch (pInfo.getDatabaseInfo().getType()) {
			case DataBaseInfo.TYPE_SQLSERVER:
				return new SQLServerBuilder();
			case DataBaseInfo.TYPE_POSTGRESQL:
				return new PostgreSQLBuilder();
			case DataBaseInfo.TYPE_ORACLE:
			case DataBaseInfo.TYPE_MYSQL:
		}
		return null;
	}
	
	public String getSQLSchema(String pSchema,String pSQL) {
		if(StringUtil.isEmptyOrSpace(pSQL)){
			return "/* --- Schema = "+ pSchema +" Without Change --- */";
		}
		StringBuffer tOut = new StringBuffer();
		tOut.append("/* --- Start modify schema = "+ pSchema +" --- */").append(CHANGE_LINE);
//		tOut.append("USE " + pSchema +symbleEndOfFunction).append(CHANGE_LINE);
		tOut.append(pSQL);
		tOut.append("/* --- End  modify schema = "+ pSchema +" --- */").append(CHANGE_LINE);
		return tOut.toString();
	}

	public String getSQLTableRemove(DBTable pTable) {
		return ignoreDropTable + "DROP TABLE " + pTable.getName() + symbleEndOfFunction+CHANGE_LINE;
	}

	public String getSQLTableAdd(DBTable pTable) {
		StringBuffer tOut = new StringBuffer();
		tOut.append("CREATE TABLE ").append(pTable.getName()).append(" (").append(CHANGE_LINE);
		for (ColumnInfo c : pTable.getTableHeader().getColumns()) {
			tOut.append(CHANGE_TAB).append(c.getName()).append(" ").append(getSQLCreateColumnType(c)).append(symbleEndOfStatment).append(StringUtil.CHANGE_LINE);
		}
		NameMap tPrimaryKeys = pTable.getTableHeader().getPrimaryKey();
		for (String name : tPrimaryKeys.getNames()) {
			tOut.append(CHANGE_TAB);
			tOut.append(getSQLPrimaryKey(name,tPrimaryKeys.getColumns(name)));
			tOut.append(symbleEndOfStatment).append(StringUtil.CHANGE_LINE);
		}
		NameMap tReference = pTable.getTableHeader().getReference();
		for (String name : tReference.getNames()) {
			tOut.append(CHANGE_TAB);
			tOut.append(getSQLReference(name,tReference.getColumns(name)));
			tOut.append(symbleEndOfStatment).append(StringUtil.CHANGE_LINE);
		}
		if(tOut.toString().endsWith((symbleEndOfStatment+CHANGE_LINE))){
			tOut = tOut.deleteCharAt(tOut.lastIndexOf(symbleEndOfStatment));
		}
		tOut.append(")").append(symbleEndOfFunction).append(CHANGE_LINE);
		NameMap tIndex = pTable.getTableHeader().getIndex();
		for (String name : tIndex.getNames()) {
			tOut.append(getSQLIndex(pTable.getName(),name,tIndex.getColumns(name)));
			tOut.append(symbleEndOfFunction).append(StringUtil.CHANGE_LINE);
		}
		NameMap tUniques = pTable.getTableHeader().getUnique();
		for (String name : tUniques.getNames()) {
			tOut.append(getSQLUnique(pTable.getName(),name,tUniques.getColumns(name)));
			tOut.append(symbleEndOfFunction).append(StringUtil.CHANGE_LINE);
		}
		return tOut.toString();
	}

	public String getSQLColumnAdd(DBTable pTable, ColumnInfo pColumnInfo) {
		StringBuffer tOut = new StringBuffer();
		tOut.append("ALTER TABLE ").append(pTable.getName()).append(" ");
		tOut.append("ADD ").append(pColumnInfo.getName()).append(" ").append(getSQLCreateColumnType(pColumnInfo)).append(symbleEndOfFunction).append(CHANGE_LINE);
		return tOut.toString();
	}

	public String getSQLColumnRemove(DBTable pTable, ColumnInfo pColumnInfo) {
		//TODO 移除欄位 應該不用移除？ 
		StringBuffer tOut = new StringBuffer();
		if (pColumnInfo instanceof ColumnFullInfo) {
			ColumnFullInfo tInfo = (ColumnFullInfo) pColumnInfo;
			if(StringUtil.isNotEmptyOrSpace(tInfo.getNullableName())){
				tOut.append("ALTER TABLE ").append(pTable.getName()).append(" ");
				tOut.append("DROP CONSTRAINT ").append(tInfo.getNullableName()).append(symbleEndOfFunction).append(CHANGE_LINE);
			}
//			if(tInfo.isPrimaryKey()){ // 可能要處理 FK
//				LOG.error("must remove PrimaryKey");
//			}
//			if(tInfo.isUnique()){     // 要移除 constraint
//				LOG.error("must remove Unique");
//			}
//			if(tInfo.isIndex()){
//				LOG.error("must remove Index");
//			}
//			if(tInfo.isReference()){
//				LOG.error("must remove Reference");
//			}
		}
		tOut.append("ALTER TABLE ").append(pTable.getName()).append(" ");
		tOut.append("DROP COLUMN ").append(pColumnInfo.getName()).append(symbleEndOfFunction).append(CHANGE_LINE);
		return tOut.toString();
	}
	
	public String getSQLColumnModify(DBTable pTable, ColumnInfo pCurrent,ColumnInfo pBase) {
		
		StringBuffer tOut = new StringBuffer();
		
		if (pCurrent instanceof ColumnFullInfo) {
			ColumnFullInfo tInfo = (ColumnFullInfo) pCurrent;
			if(StringUtil.isNotEmptyOrSpace(tInfo.getNullableName())){
				tOut.append("ALTER TABLE ").append(pTable.getName()).append(" ");
				tOut.append("DROP CONSTRAINT ").append(tInfo.getNullableName()).append(symbleEndOfFunction).append(CHANGE_LINE);
			}
		}
		
		tOut.append("ALTER TABLE ").append(pTable.getName()).append(" ");
		tOut.append("ALTER COLUMN ").append(pBase.getName()).append(" ").append(getSQLAlterColumnType(pBase)).append(symbleEndOfFunction).append(CHANGE_LINE);
		
		if (pBase instanceof ColumnFullInfo) {
			ColumnFullInfo tInfo = (ColumnFullInfo) pCurrent;
			if(StringUtil.isNotEmptyOrSpace(tInfo.getNullableName())){
				tOut.append("ALTER TABLE ").append(pTable.getName()).append(" ");
				tOut.append("ADD CONSTRAINT ").append(tInfo.getNullableName()).append(" ");
				tOut.append("DEFAULT ").append(tInfo.getRawDefault()).append(" ");
				tOut.append("FOR").append(" ").append(tInfo.getName()).append(symbleEndOfFunction).append(CHANGE_LINE);
			}
		}
		
		return tOut.toString();
	}	
	public String getSQLAlterColumnType(ColumnInfo pColumnInfo) {
		String tOut = pColumnInfo.getRawTypeName();
		if(pColumnInfo.getRawType() == Types.VARCHAR){
			tOut += "("+pColumnInfo.getSize()+")";
		}
		tOut += " " + (pColumnInfo.isNullable() ? "NULL" : "NOT NULL");
		return tOut;
	}
	public String getSQLCreateColumnType(ColumnInfo pColumnInfo){
		String tOut = pColumnInfo.getRawTypeName();
		if(pColumnInfo.getRawType() == Types.VARCHAR){
			tOut += "("+pColumnInfo.getSize()+")";
		}
		tOut += " " + (pColumnInfo.isNullable() ? "NULL" : "NOT NULL");
		if(StringUtil.isNotEmptyOrSpace(pColumnInfo.getRawDefault())){
			tOut += " DEFAULT " + pColumnInfo.getRawDefault();
		}
		return tOut;
	}
	
	public String getSQLIndex(String pTableName,String pName, List<NameMapCell> pColumns) {
		StringBuffer tOut = new StringBuffer();
		tOut.append("CREATE NONCLUSTERED INDEX ").append(pName).append(" ON ").append(pTableName).append("(").append(StringUtil.listToString(pColumns)).append(")");
		return tOut.toString();
	}
	
	public String getSQLUnique(String pTableName,String pName,List<NameMapCell> pColumns) {
		StringBuffer tOut = new StringBuffer();
		tOut.append("ALTER TABLE ").append(pTableName).append(" ");
		tOut.append("ADD CONSTRAINT ").append(pName).append(" ");
		tOut.append("UNIQUE (").append(StringUtil.listToString(pColumns)).append(")");
		return tOut.toString();
	}

	public String getSQLPrimaryKey(String pName,List<NameMapCell> pColumns) {
		StringBuffer tOut = new StringBuffer();
		tOut.append("CONSTRAINT ").append(pName).append(" ");
		tOut.append("PRIMARY KEY (").append(StringUtil.listToString(pColumns)).append(")");
		return tOut.toString();
	}

	public String getSQLReference(String pName, List<NameMapCell> pColumns) {
		StringBuffer tOut = new StringBuffer();
		NameMapCell tCell = pColumns.get(0);
		tOut.append("CONSTRAINT ").append(pName).append(" ");
		tOut.append("FOREIGN KEY (").append(tCell.getName()).append(") ");
		tOut.append("REFERENCES ").append(tCell.getRefTable()).append("(").append(tCell.getRefColumn()).append(")");
		return tOut.toString();
	}
	
	public String getSQLConstraintRemove(DBTable pCurrent, DBTable pBase) {
		StringBuffer tOut = new StringBuffer();
		if(Configuration.getInstance().isCheckPrimaryKey()){
			NameMap tCurrentPrimaryKey = pCurrent.getTableHeader().getPrimaryKey();
			NameMap tBasePrimaryKey = pBase.getTableHeader().getPrimaryKey();
			tOut.append(dropConstraint(pCurrent, tCurrentPrimaryKey, tBasePrimaryKey));
		}
		if(Configuration.getInstance().isCheckUnique()){
			NameMap tCurrentUnique = pCurrent.getTableHeader().getUnique();
			NameMap tBaseUnique = pBase.getTableHeader().getUnique();
			tOut.append(dropUnique(pCurrent,tCurrentUnique, tBaseUnique));
		}
		if(Configuration.getInstance().isCheckIndex()){
			NameMap tCurrentIndex = pCurrent.getTableHeader().getIndex();
			NameMap tBaseIndex = pBase.getTableHeader().getIndex();
			tOut.append(dropIndex(pCurrent,tCurrentIndex, tBaseIndex));
		}
		if(Configuration.getInstance().isCheckForeignKey()){
			NameMap tCurrentReference = pCurrent.getTableHeader().getReference();
			NameMap tBaseReference = pBase.getTableHeader().getReference();
			tOut.append(dropConstraint(pCurrent,tCurrentReference, tBaseReference));
		}
		return tOut.toString();
	}

	public String dropIndex(DBTable pTable, NameMap pCurrent,NameMap pBase) {
		StringBuffer tOut = new StringBuffer();
		if(!pCurrent.isSame(pBase)){
			Map<String,List<NameMapCell>> tKey = pCurrent.getSubstract(pBase);
			for (String s : tKey.keySet()) {
				tOut.append("DROP INDEX ").append(pTable.getName()).append(".").append(s).append(symbleEndOfFunction).append(CHANGE_LINE);
			}
		}
		return tOut.toString();
	}

	private String dropConstraint(DBTable pTable, NameMap pCurrent, NameMap pBase) {
		StringBuffer tOut = new StringBuffer();
		if(!pCurrent.isSame(pBase)){
			Map<String,List<NameMapCell>> tKey = pCurrent.getSubstract(pBase);
			for (String s : tKey.keySet()) {
				tOut.append("ALTER TABLE ").append(pTable.getName()).append(" ");
				tOut.append("DROP CONSTRAINT ").append(s).append(symbleEndOfFunction).append(CHANGE_LINE);
			}
		}
		return tOut.toString();
	}
	public String dropUnique(DBTable pTable, NameMap pCurrent, NameMap pBase) {
		return dropConstraint(pTable, pCurrent, pBase);
	}
	public String getSQLConstraintAdd(DBTable pCurrent, DBTable pBase) {
		StringBuffer tOut = new StringBuffer();
		if(Configuration.getInstance().isCheckPrimaryKey()){
			NameMap tCurrentPrimaryKey = pCurrent.getTableHeader().getPrimaryKey();
			NameMap tBasePrimaryKey = pBase.getTableHeader().getPrimaryKey();
			tOut.append(addConstraintPrimaryKey(pBase, tCurrentPrimaryKey,tBasePrimaryKey));
		}
		if(Configuration.getInstance().isCheckUnique()){
			NameMap tCurrentUnique = pCurrent.getTableHeader().getUnique();
			NameMap tBaseUnique    = pBase.getTableHeader().getUnique();
			tOut.append(addConstraintUnique(pBase,tCurrentUnique, tBaseUnique));
		}
		if(Configuration.getInstance().isCheckIndex()){
			NameMap tCurrentIndex = pCurrent.getTableHeader().getIndex();
			NameMap tBaseIndex = pBase.getTableHeader().getIndex();
			tOut.append(addConstraintIndex(pBase,tCurrentIndex, tBaseIndex));
		}
		if(Configuration.getInstance().isCheckForeignKey()){
			NameMap tCurrentReference = pCurrent.getTableHeader().getReference();
			NameMap tBaseReference = pBase.getTableHeader().getReference();
			tOut.append(addConstraintReference(pBase,tCurrentReference, tBaseReference));
		}
		return tOut.toString();
	}

	private String addConstraintUnique(DBTable pCurrent,NameMap tCurrentUnique, NameMap tBaseUnique) {
		StringBuffer tOut = new StringBuffer();
		if(!tBaseUnique.isSame(tCurrentUnique)){
			Map<String,List<NameMapCell>> tUnique = tBaseUnique.getSubstract(tCurrentUnique);
			for (String s : tUnique.keySet()) {
				tOut.append(getSQLUnique(pCurrent.getName(),s, tUnique.get(s))).append(symbleEndOfFunction).append(CHANGE_LINE);
			}
		}
		return tOut.toString();
	}

	private String addConstraintPrimaryKey(DBTable pCurrent,NameMap tCurrentPrimaryKey, NameMap tBasePrimaryKey) {
		StringBuffer tOut = new StringBuffer();
		if(!tBasePrimaryKey.isSame(tCurrentPrimaryKey)){
			Map<String,List<NameMapCell>> tPrimaryKey = tBasePrimaryKey.getSubstract(tCurrentPrimaryKey);
			for (String s : tPrimaryKey.keySet()) {
				tOut.append("ALTER TABLE ").append(pCurrent.getName()).append(" ");
				tOut.append("ADD ").append(getSQLPrimaryKey(s, tPrimaryKey.get(s))).append(symbleEndOfFunction).append(CHANGE_LINE);
			}
		}
		return tOut.toString();
	}
	
	private String addConstraintIndex(DBTable pBase, NameMap tCurrentIndex,NameMap tBaseIndex) {
		StringBuffer tOut = new StringBuffer();
		if(!tBaseIndex.isSame(tCurrentIndex)){
			Map<String,List<NameMapCell>> tIndex = tBaseIndex.getSubstract(tCurrentIndex);
			for (String s : tIndex.keySet()) {
				tOut.append(getSQLIndex(pBase.getName(),s, tIndex.get(s))).append(symbleEndOfFunction).append(CHANGE_LINE);
			}
		}
		return tOut.toString();
	}
	
	private String addConstraintReference(DBTable pBase,NameMap tCurrentReference, NameMap tBaseReference) {
		StringBuffer tOut = new StringBuffer();
		if(!tBaseReference.isSame(tCurrentReference)){
			Map<String,List<NameMapCell>> tReference = tBaseReference.getSubstract(tCurrentReference);
			for (String s : tReference.keySet()) {
				tOut.append(ignoreAlterReference).append("ALTER TABLE ").append(pBase.getName()).append(" ");
				tOut.append("ADD ").append(getSQLReference(s,tReference.get(s))).append(symbleEndOfFunction).append(CHANGE_LINE);
			}
		}
		return tOut.toString();
	}
}